A database is a collection of logically related data arranged in a predetermined format, such as in tables that contain rows and columns. To access the content of a table in the database, queries according to a standard database query language (e.g., Structured Query Language or SQL) are submitted to the database system. A query can be issued to insert new entries into a table of a database (suggest to insert a row into the table), modify the content of the table, or to delete entries from the table.
Examples of SQL statements include INSERT, SELECT, UPDATE, and DELETE. The SELECT statement is used to retrieve information from the database and to organize information for presentation to a user or to an application program. A SELECT statement can also include a GROUP BY clause, which specifies a grouping function to group output results according to specified one or more attributes (columns). In the ensuing discussion, the term “attribute” and “column” are used interchangeably. The output of the group-by operation is a set of groups of rows where each group is a set of rows that share a common value of the grouping attribute(s). The grouping attribute(s) are the attribute(s) specified in the GROUP BY clause.
As the technology of storage devices and database software have improved, the capacity of database systems have also increased dramatically. An application of database systems is data warehousing, where data from various sources are collected and stored in the data warehouse. The amount of data that can be stored in the data warehouse can be immense. To process information within such data warehouses, on-line analytical processing (OLAP) is typically performed. Usually, on-line analytical processing involves the calculation of aggregates on large data sets. Examples of aggregates include the calculation of a sum of values on a given attribute, the calculation of an average, the calculation of a minimum, the calculation of a maximum, the counting of a number of rows, and so forth.
Conventionally, a convenient mechanism has not been provided to enable the calculation of percentages using database queries. Typically, a user has to manually specify how the percentage is to be performed by using relatively complicated clauses in the database query, such as the OVER and PARTITION clauses proposed in the SQL-OLAP amendment by the American National Standards Institute (ANSI). Such clauses for calculating percentages are relatively complex to use, which may result in errors in calculating percentages if the user does not properly specify the clauses. Alternatively, a user can also combine traditional SQL aggregations for calculating percentages; however, this approach also results in complex queries that are unwieldy to a user. Another issue associated with conventional techniques of computing percentages is that divisions by zero result in errors.